<?php
/**
* Tries to walk over the database and detect the PEAR people
* to posts
*/
require_once 'MDB2.php';


class QA_PEAR_Activitytracker_PeopleFinder
{
    protected $db = null;

    const REGEX_EMAIL = '[a-z0-9.-]+@(?:[a-z0-9-]+.)+[a-z0-9]{2,3}';



    public function __construct(array $arConfig)
    {
        //PEAR::setErrorHandling(PEAR_ERROR_EXCEPTION);
        $this->db = MDB2::connect($arConfig['dsn']);
        $this->db->setFetchMode(MDB2_FETCHMODE_OBJECT);
        $this->db->loadModule('Extended', null, false);
    }



    /**
    * Walks over posts without username and an @php.net address, extracts
    * email address and name and assigns the name to the post.
    * Also adds the email address and name if they are not in the database
    * yet.
    */
    public function findPhpPeople()
    {
        $sqlFetchFrom = 'SELECT post_id, post_from FROM posts'
            . ' WHERE post_username IS NULL AND post_from LIKE "%@php.net%"'
            . ' ';
        $res = $this->db->query($sqlFetchFrom);
        while ($row = $res->fetchRow()) {
            $arData = self::splitFrom($row->post_from);
            if ($arData['email'] === null) {
                continue;
            }
            $strUsername = $this->findUsername($arData['email'], $arData['name']);
            if ($strUsername === null) {
                //no username? strange. let it be for now
                continue;
            }
            $this->registerName($strUsername, $arData['name']);
            $this->registerEmail($strUsername, $arData['email']);

            //make username
            $sqlSetName = 'UPDATE posts SET post_username = ' . $this->db->quote($strUsername)
                . ' WHERE post_id = ' . intval($row->post_id);
            $this->db->query($sqlSetName);
        }
    }//public function findPhpPeople()



    /**
    * Finds the pear username for a given email address and name
    */
    public function findUsername($strEmail, $strName)
    {
        //FIXME: local caching
        //email
        $sqlEmail = 'SELECT add_username FROM addresses'
            . ' WHERE add_email = ' . $this->db->quote($strEmail);
        $strUsername = $this->db->getOne($sqlEmail);

        if ($strUsername !== null) {
            return $strUsername;
        }

        //name only?
        $sqlEmail = 'SELECT nam_username FROM names'
            . ' WHERE nam_name = ' . $this->db->quote($strName);
        $arNames = $this->db->getAll($sqlEmail);

        if (count($arNames) == 1) {
            return reset($arNames);
        }

        //@php.net address? then we've got the name
        if (substr($strEmail, -8) == '@php.net') {
            return substr($strEmail, 0, -8);
        }

        return null;
    }//public function findUsername($strEmail, $strName)



    public function registerName($strUsername, $strName)
    {
        $sqlInsert = 'INSERT INTO names (nam_username, nam_name)'
            . ' VALUES(' . $this->db->quote($strUsername) . ','
            . $this->db->quote($strName) . ')';
        $this->db->query($sqlInsert);
    }//public function registerName($strUsername, $strName)



    public function registerEmail($strUsername, $strEmail)
    {
        $sqlInsert = 'INSERT INTO addresses (add_username, add_email)'
            . ' VALUES(' . $this->db->quote($strUsername) . ','
            . $this->db->quote($strEmail) . ')';
        $this->db->query($sqlInsert);
    }//public function registerName($strUsername, $strEmail)



    /**
    * Splits a "from" header into name and email address
    *
    * Detects following formats:
    * - e@m.il (Foo Bar)
    * - e@m.il ((Foo Bar))
    * - e@m.il ("Foo Bar")
    * - e@m.il
    * - Foo Bar <e@m.il>
    * - e@m.il (e2@m.il)  -> e2@m.il has precedence
    *
    * @param string $strFrom From header line
    *
    * @return array Array with email and name (both keys)
    */
    public static function splitFrom($strFrom)
    {
        $strFrom  = trim($strFrom);
        $arResult = array('email' => null, 'name' => null);

        //catch email
        if (!preg_match_all('#' . self::REGEX_EMAIL . '#i', $strFrom, $arMatches)) {
            //no email?
            return $arResult;
        }
        $arResult['email'] = $arMatches[0][0];

        $strRest = trim(
            str_replace(
                array($arResult['email'], '<', '>', '(', ')', '"', "'"),
                '',
                $strFrom
            )
        );

        if ($strRest == '') {
            return $arResult;
        }

        if (preg_match('#^' . self::REGEX_EMAIL . '$#i', $strRest)) {
            $arResult['email'] = $strRest;
            return $arResult;
        }

        $arResult['name'] = $strRest;

        return $arResult;
    }//public static function splitFrom($strFrom)



    /**
    * Fixes a name (Forename Surname).
    * Returns NULL for single names ("someword") and fixes
    * french notation (SURNAME Forename)
    *
    * @param string $strName Name
    *
    * @return string Normalized name
    */
    public static function normalizeName($strName)
    {
        if ($strName == null) {
            return null;
        }

        //single word
        if (strpos($strName, ' ') === false) {
            return null;
        }

        $arParts = explode(' ', $strName);

        //sentence
        if (count($arParts) > 3) {
            return null;
        }

        if (strtoupper($arParts[0]) == $arParts[0]) {
            //french surname first
            $strSurname = ucfirst(strtolower($arParts[0]));
            array_shift($arParts);
            $strName = implode(' ', $arParts) . ' ' . $strSurname;
        } else if (strtoupper($arParts[count($arParts)-1]) == $arParts[count($arParts)-1]) {
            //french surname last
            $arParts[count($arParts)-1] = ucfirst(strtolower($arParts[count($arParts)-1]));
            $strName = implode(' ', $arParts);
        }

        return $strName;
    }//public static function normalizeName($strName)

}//class QA_PEAR_Activitytracker_PeopleFinder

?>